﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace org.maquech.CEeL.Core.EducationalProgram {
   public class Term : PersistentObject {
        #region Fields and Properties

        /// <summary>
        /// The Term's name field.
        /// </summary>
        private string name;

        /// <summary>
        /// The Term's name.
        /// </summary>
        public string Name {
            get {
                return this.name;
            }
            set {
                if ( this.name != value )
                    modifiedDBFields.Add( "name" );
                this.name = value;
            }
        }

        /// <summary>
        /// The Term's name duration field.
        /// </summary>
        private int duration;

        /// <summary>
        /// The Term's name duration.
        /// </summary>
        public int Duration {
            get {
                return this.duration;
            }
            set {
                if ( this.duration != value )
                    modifiedDBFields.Add( "duration" );
                this.duration = value;
            }
        }

        private TimeUnit timeUnit;

        public TimeUnit TimeUnit {
            get {
                return this.timeUnit;
            }
            set {
                if ( !this.timeUnit.Equals( value ) )
                    modifiedDBFields.Add( "time_unit_id" );
                this.timeUnit = value;
            }
        }

        /// <summary>
        /// Checks if the current instance is valid to be stored in the database.
        /// </summary>
        public override bool IsValid {
            get {
                return !( this.name == null ) && ( this.name.Trim( ) != "" )
                    && this.duration >= 0
                    && this.timeUnit.IsValid;
            }
        }
        #endregion

        #region Constructors
        public Term ( ) {
            this.id = -1;
            this.name = "";
            this.duration = 0;
            this.timeUnit = new TimeUnit( );
            this.isNew = true;
        }

        public Term ( int id, string name, int duration, TimeUnit timeUnit ) {
            this.id = id;
            this.name = name;
            this.duration = duration;
            this.timeUnit = timeUnit;
            this.isNew = ( id == -1 );
        }

        public Term ( string name, int duration, TimeUnit timeUnit )
            : this( -1, name, duration, timeUnit ) {
        }
        #endregion

        #region Methods

        #region System.Object Methods
        /// <summary>
        /// Checks if the object is equal to the current instance.
        /// </summary>
        /// <param name="obj">The object to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public override bool Equals ( object obj ) {
            if ( obj == null ) {
                return false;
            }
            Term t = obj as Term;
            if ( ( object ) t == null ) {
                return false;
            }
            return ( this.name == t.name )
                && ( this.duration == t.duration )
                && this.timeUnit.Equals( t.timeUnit );
        }

        /// <summary>
        /// Checks if the Term is equal to the current instance.
        /// </summary>
        /// <param name="a">The Term to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public bool Equals ( Term t ) {
            if ( ( object ) t == null ) {
                return false;
            }
            return ( this.name == t.name )
                && ( this.duration == t.duration )
                && this.timeUnit.Equals( t.timeUnit );
        }

        public override int GetHashCode ( ) {
            return id.GetHashCode( )
                ^ this.name.GetHashCode( )
                ^ this.duration.GetHashCode( )
                ^ this.timeUnit.GetHashCode( );
        }

        /// <summary>
        /// String representation of the current instance.
        /// </summary>
        /// <returns>A string representation of the current instance.</returns>
        public override string ToString ( ) {
            StringBuilder bldr = new StringBuilder( );
            object [ ] objArr = new object [ ] { this.id, this.duration, this.name, this.timeUnit.Abbreviation };
            bldr.AppendFormat( "[{0}] {2}. Duration: {1} {3}", objArr );
            return bldr.ToString( );
        }
        #endregion

        /// <summary>
        /// Saves the current instance to the database.
        /// </summary>
        /// <returns><code>True</code> if the object was saved to the database, <code>false</code> otherwise.</returns>
        public override bool Save ( ) {
            bool saved = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;

            try {
                sqlCommand = "SELECT count(*) = 0 as exists FROM terms WHERE id = @id OR name ILIKE @name";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                isNew = ( bool ) db.ExecuteScalar( dbCommand );
                if ( isNew ) {
                    sqlCommand = "SELECT nextval('terms_id_seq')";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    this.id = ( int ) db.ExecuteScalar( dbCommand );

                    sqlCommand = "INSERT INTO terms ( id, name, duration, time_unit_id )"
                       + " VALUES ( @id, @name, @duration, @ordering )";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                    db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                    db.AddInParameter( dbCommand, "@duration", DbType.Int32, this.duration );
                    db.AddInParameter( dbCommand, "@time_unit_id", DbType.Int32, this.timeUnit.Id );
                    saved = db.ExecuteNonQuery( dbCommand ) > 0;
                }
                else if ( this.modifiedDBFields.Count > 0 ) {
                    sqlCommand = "UPDATE terms SET name = @name, duration = @abbreviaton, time_unit_id = @time_unit_id,"
                        + " WHERE id = @id";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                    db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                    db.AddInParameter( dbCommand, "@duration", DbType.String, this.duration );
                    db.AddInParameter( dbCommand, "@time_unit_id", DbType.Int32, this.timeUnit.Id );
                    saved = db.ExecuteNonQuery( dbCommand ) > 0;
                }
                else {
                    //No update or insert needed
                    saved = true;
                }
            }
            catch ( Exception e ) {
                saved = false;
                this.errorMessages.Add( "There was an error saving the Term: " + e.Message );
            }
            return saved;
        }

        /// <summary>
        /// Deletes the current object from the database.
        /// </summary>
        /// <returns><code>True</code> if the object was deleted from the database, <code>false</code> otherwise.</returns>
        public override bool Delete ( ) {
            bool deleted = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;
            try {
                sqlCommand = "DELETE FROM terms WHERE id = @id";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                deleted = db.ExecuteNonQuery( dbCommand ) >= 0;
            }
            catch ( Exception e ) {
                deleted = false;
                this.errorMessages.Add( "There was an error saving the Term: " + e.Message );
            }
            return deleted;
        }


        #region Load Methods
        /// <summary>
        /// Loads the Term with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The Terms' id.</param>
        /// <returns>A Term object with it's information or a null Term object if the <code>id</code> was not present in the database.</returns>
        public static Term Load ( int id ) {
            Term t = null;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT t.name, t.duration, t.time_unit_id,"
                + " tu.name AS name_tu, tu.abbreviation AS abbreviation_tu"
                + " FROM terms AS t, time_units AS tu"
                + " WHERE t.id = @id AND t.time_unit_id = tu.id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            try {
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    if ( rdr.Read( ) ) {
                        TimeUnit tu = new TimeUnit( ( int ) rdr [ "time_unit_id" ], ( string ) rdr [ "name_tu" ], ( string ) rdr [ "abbreviation_tu" ] );
                        t = new Term( id, ( string ) rdr [ "name" ], ( int ) rdr [ "duration" ], tu );
                        t.modifiedDBFields.Clear( );
                        t.ClearMessages( );
                        t.isNew = false;
                    }
                    rdr.Close( );
                }
            }
            catch ( Exception e ) {
                t.errorMessages.Add( string.Format( "There was an error loading the Term with ID {0}: {1}", id.ToString( ), e.Message ) );
            }
            return t;
        }

        /// <summary>
        /// Loads the Term with the given <code>name</code>.
        /// </summary>
        /// <param name="id">The Terms' name.</param>
        /// <returns>A Term object with it's information or a null Term object if the <code>name</code> was not present in the database.</returns>
        public static Term LoadByName ( string name ) {
            Term t = null;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT t.id, t.name, t.duration, t.time_unit_id,"
                + " tu.name AS name_tu, tu.abbreviation AS abbreviation_tu"
                + " FROM terms AS t, time_units AS tu"
                + " WHERE t.id = @id AND t.time_unit_id = tu.id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@name", DbType.String, name );
            try {
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    if ( rdr.Read( ) ) {
                        TimeUnit tu = new TimeUnit( ( int ) rdr [ "time_unit_id" ], ( string ) rdr [ "name_tu" ], ( string ) rdr [ "abbreviation_tu" ] );
                        t = new Term( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( int ) rdr [ "duration" ], tu );
                        t.modifiedDBFields.Clear( );
                        t.ClearMessages( );
                        t.isNew = false;
                    }
                    rdr.Close( );
                }
            }
            catch ( Exception e ) {
                t.errorMessages.Add( string.Format( "There was an error loading the Term {0}: {1}", name, e.Message ) );
            }
            return t;
        }

        /// <summary>
        /// Checks the database for existence of the Term with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The Terms' id.</param>
        /// <returns><code>True</code> if the term with the given <code>id</code>exists, <code>false</code> otherwise.</returns>
        public static bool Exists ( int id ) {
            bool exists = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 as exists FROM terms WHERE id = @id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            exists = ( bool ) db.ExecuteScalar( dbCommand );
            return exists;
        }

        /// <summary>
        /// Checks the database for existence of the Term with the given <code>name</code>.
        /// </summary>
        /// <param name="name">The Terms' name</param>
        /// <returns><code>True</code> if the term with the given <code>name</code>exists, <code>false</code> otherwise.</returns>
        public static bool ExistsByName ( string name ) {
            bool exists = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 as exists FROM terms WHERE name ILIKE @name";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@name", DbType.String, name );
            exists = ( bool ) db.ExecuteScalar( dbCommand );
            return exists;
        }

        /// <summary>
        /// Loads all of the Terms in the database to a list.
        /// </summary>
        /// <returns>A List containing all of the Terms on the database.</returns>
        public static List<Term> LoadAll ( ) {
            List<Term> tl = new List<Term>( );
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT t.id, t.name, t.duration, t.time_unit_id,"
                + " tu.name AS name_tu, tu.abbreviation AS abbreviation_tu"
                + " FROM terms AS t, time_units AS tu"
                + " WHERE t.id = @id AND t.time_unit_id = tu.id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                TimeUnit tu;
                while ( rdr.Read( ) ) {
                    tu = new TimeUnit( ( int ) rdr [ "time_unit_id" ], ( string ) rdr [ "name_tu" ], ( string ) rdr [ "abbreviation_tu" ] );
                    tl.Add( new Term( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( int ) rdr [ "duration" ], tu ) );
                }
                rdr.Close( );
            }
            return tl;
        }
        #endregion
        #endregion
    }
}
